#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2024/06/25 17:53
# @Author : 郭晗
# @File    : sku_filter.py
import json
from common.setting import ensure_path_sep
from utils.mysqlUtils.db_pool import Database
import os
import time
from common.setting import ensure_path_sep
from utils.logging_tool.log_control import LogHandler
from utils.mysqlUtils.db_pool import Database


current_path = os.path.abspath(__file__)
current_dir = os.path.dirname(current_path)
path_list = current_path.split("\\")
dis_path = "\\".join(path_list[-2:-1])


def remove_file(info_path):
    if os.path.exists(info_path):
        os.remove(info_path)
    else:
        print(f"{info_path} 不存在")


info_path = ensure_path_sep(f"\\logs\\info-{dis_path}{time.time()}")
error_path = ensure_path_sep(f"\\logs\\\error-{dis_path}{time.time()}.log")
warning_path = ensure_path_sep(f"\\logs\\warning-{dis_path}{time.time()}.log")
remove_file(info_path)
remove_file(error_path)
remove_file(warning_path)
INFO = LogHandler(info_path, level='info')
ERROR = LogHandler(error_path, level='error')
WARNING = LogHandler(warning_path, level='warning')



class PublishStrategy:

    db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")

    def __init__(self,strategy_id):
        self.strategy_id = strategy_id
        # 查询策略
        get_stragety = """
                SELECT id,source_type,application,target_source,execute_condition,product_template_id,listing_template_id \
                FROM test_zhcxkj_center_strategy_comprehensive.`t_walmart_publish_strategy` WHERE `status`=0 AND is_deleted=0 AND id = %s
                        """
        strategy = self.db.fetch_all_tuple(get_stragety, (self.strategy_id,))
        if strategy:
            self.source_type = strategy[0][1]
            self.application = strategy[0][2]
            target_source = eval(strategy[0][3])
            self.source_ids = [d['sourceId'] for d in target_source]
            self.minQuality = eval(strategy[0][4])['strategyListing'].get('minQuality')
            self.maxQuality = eval(strategy[0][4])['strategyListing'].get('maxQuality')
            self.skuPublishCountLimit = eval(strategy[0][4])['strategyListing'].get('skuPublishCountLimit')
            self.listingPublishCountLimit = eval(strategy[0][4])['strategyListing'].get('listingPublishCountLimit')
            self.amazonCategoryIdList = eval(strategy[0][4]).get('amazonCategoryIdList')
            self.product_template_id = strategy[0][5]
            self.listing_template_id = strategy[0][6]


    def get_basices_sku_set(self):
        if self.application == 2:
            get_product_sku = """
                SELECT sku FROM `test_zhcxkj_center_strategy_comprehensive`.`t_walmart_publish_strategy_sku` 
                WHERE `strategy_id` = %s AND `is_deleted` = '0'
                            """
            product_sku_res = self.db.fetch_all_tuple(get_product_sku, (self.strategy_id,))
            product_sku_set = set(map(lambda x: x[0], product_sku_res))
            if not product_sku_set:
                raise Exception("未查到策略指定的sku")

            # listing模板
            if self.listing_template_id:
                product_sku_set = set(filter(lambda x: x in self.get_listing_sku_set(), product_sku_set))
            # 否定规则
            product_sku_set = set(filter(lambda x: x not in self.get_negative_sku(), product_sku_set))
            # 店铺可刊登分类
            product_sku_set = self.get_sku_category(product_sku_set)
            # 对应渠道上架次数
            if self.skuPublishCountLimit:
                product_sku_set = self.get_publish_count(product_sku_set)
            # 平台总刊登次数(未考虑队列冷数据)
            if self.listingPublishCountLimit:
                product_sku_set = self.get_listing_publish_count(product_sku_set)
            # 策略配置分类
            if self.amazonCategoryIdList:
                product_sku_set = self.get_strategy_amazonCategoryIdList(product_sku_set)
            # 可用库存(渠道所配置的仓库下各仓库分开算的库存总和)
            if self.minQuality or self.maxQuality:
                result = self.get_good_num(product_sku_set)
                if self.minQuality and self.maxQuality:
                    product_sku_set = set(filter(lambda x: self.minQuality <= x[1] <= self.maxQuality, result))
                if self.minQuality and self.maxQuality is None:
                    product_sku_set = set(filter(lambda x: self.minQuality <= x[1], result))
                if self.minQuality is None and self.maxQuality:
                    product_sku_set = set(filter(lambda x: x[1] <= self.maxQuality, result))
                product_sku_set = set(map(lambda x: x[0], product_sku_set))

            # listing在线sku过滤
            product_sku_set = self.get_listing(product_sku_set)
            # 队列中数据过滤
            product_sku_set = self.get_queue(product_sku_set)

            print(f"product_sku_total={product_sku_set}共计{len(product_sku_set)}个")

        else:
            get_product_sku = """
                    SELECT id,sku FROM `test_zhcxkj_center_strategy_comprehensive`.`t_shopee_product_rules_sku` \
                    WHERE `product_rules_template_id` = %s AND id>%s ORDER BY id limit 1000 
                                """

            id_index = 0
            product_sku_total = set()
            while True:
                print(f"index={id_index}")
                product_sku_res = self.db.fetch_all_tuple(get_product_sku, (self.product_template_id,id_index,))
                if not len(product_sku_res):
                    break
                id_index = product_sku_res[-1][0]
                product_sku_set = set(map(lambda x: x[1], product_sku_res))
                if not product_sku_set:
                    continue
                # listing模板
                if self.listing_template_id:
                    product_sku_set = set(filter(lambda x: x in self.get_listing_sku_set(), product_sku_set))
                if not product_sku_set:
                    continue
                # 否定规则
                product_sku_set = set(filter(lambda x: x not in self.get_negative_sku(), product_sku_set))
                if not product_sku_set:
                    continue
                # 店铺可刊登分类
                product_sku_set = self.get_sku_category(product_sku_set)
                if not product_sku_set:
                    continue
                # 对应渠道上架次数
                if self.skuPublishCountLimit:
                    product_sku_set = self.get_publish_count(product_sku_set)
                if not product_sku_set:
                    continue
                # 平台总刊登次数(未考虑队列冷数据)
                if self.listingPublishCountLimit:
                    product_sku_set = self.get_listing_publish_count(product_sku_set)
                if not product_sku_set:
                    continue
                # 策略配置分类
                if self.amazonCategoryIdList:
                    product_sku_set = self.get_strategy_amazonCategoryIdList(product_sku_set)
                if not product_sku_set:
                    continue

                # 可用库存(渠道所配置的仓库下各仓库分开算的库存总和)
                if self.minQuality or self.maxQuality:
                    result = self.get_good_num(product_sku_set)
                    if self.minQuality and self.maxQuality:
                        product_sku_set = set(filter(lambda x: self.minQuality <= x[1] <= self.maxQuality, result))
                    if self.minQuality and self.maxQuality is None:
                        product_sku_set = set(filter(lambda x: self.minQuality <= x[1], result))
                    if self.minQuality is None and self.maxQuality:
                        product_sku_set = set(filter(lambda x: x[1] <= self.maxQuality, result))
                    product_sku_set = set(map(lambda x: x[0], product_sku_set))
                if not product_sku_set:
                    continue
                # listing在线sku过滤
                product_sku_set = self.get_listing(product_sku_set)
                if not product_sku_set:
                    continue
                # 队列中数据过滤
                product_sku_set = self.get_queue(product_sku_set)
                if not product_sku_set:
                    continue



                product_sku_total |= product_sku_set
            print(f"product_sku_total={product_sku_total}共计{len(product_sku_total)}个")

            queue_sku="""
                SELECT sku FROM `test_zhcxkj_center_strategy_comprehensive`.`t_walmart_publish_strategy_execute_queue_detail` 
                WHERE strategy_id=%s AND task_id = (SELECT MAX(task_id) FROM `test_zhcxkj_center_strategy_comprehensive`.
                `t_walmart_publish_strategy_execute_queue_detail` WHERE strategy_id=%s)
                            """
            queue_sku_res = self.db.fetch_all_tuple(queue_sku, (self.strategy_id, self.strategy_id,))
            queue_sku_res = set(map(lambda x: x[0], queue_sku_res))
            if product_sku_total == queue_sku_res:
                pass
            else:
                ERROR.logger.error(f"错误，差异如下")
                print(f"{product_sku_total - queue_sku_res}")
                print(f"{queue_sku_res - product_sku_total}")



    def get_publish_limit(self):
        publish_limit = """
                SELECT day_publish_remain_limit FROM `test_zhcxkj_center_strategy_comprehensive`.`t_walmart_publish_limit`
                WHERE source_id = %s
                        """
        publish_limit_res = self.db.fetch_all_tuple(publish_limit, (self.source_ids,))


    def get_listing_sku_set(self):
        if self.listing_template_id:
            listing_template_sku = """
                SELECT sku FROM `test_zhcxkj_center_strategy_comprehensive`.`t_walmart_listing_rules_template_sku` WHERE template_id = %s  GROUP BY sku
                        """
            listing_template_sku_res = self.db.fetch_all_tuple(listing_template_sku, (self.listing_template_id,))
            listing_template_sku_set = set(map(lambda x: x[0], listing_template_sku_res))
            return listing_template_sku_set

    def get_negative_sku(self):
        # negative_sku = """
        #         SELECT negative_object FROM `test_zhcxkj_center_strategy_comprehensive`.`t_walmart_negation_rule_relation`
        #         WHERE `source_type` = %s AND `labels` LIKE '%6%' AND `negative_type` = '0' AND `is_deleted` = '0'
        #                 """
        negative_sku = """
                SELECT tl.negative_object FROM `test_zhcxkj_center_strategy_comprehensive`.`t_walmart_negation_rule_template` t
                LEFT JOIN `test_zhcxkj_center_strategy_comprehensive`.`t_walmart_negation_rule_relation` tl
                ON t.id= tl.negation_rule_id WHERE t.source_type= %s AND t.auto_publish=1
                AND t.negative_type=0 AND t.`status`=1 AND tl.is_deleted=0
                        """
        negative_sku_res = self.db.fetch_all_tuple(negative_sku, (self.source_type,))
        negative_sku_set = set(map(lambda x: x[0], negative_sku_res))
        return negative_sku_set


    def get_source_category(self):
        source_category="""
                SELECT other_category_id FROM `test_zhcxkj_center_category`.`t_order_source_category` 
                WHERE `platform_type` = '45' AND `order_source_id` = %s
                        """
        source_category_res = self.db.fetch_all_tuple(source_category, (self.source_ids,))
        source_category_set = set(map(lambda x: x[0], source_category_res))
        return source_category_set


    def get_strategy_amazonCategoryIdList(self,product_sku):
        strategy_amazonCategoryIdList = self.amazonCategoryIdList
        get_sku_category = """
                SELECT sku,other_category_id,amazon_category_id  FROM `test_zhcxkj_center_category`.`t_sku_category` WHERE `platform_type` = '45' AND `sku` IN %s
                                """
        get_sku_category_res = self.db.fetch_all_tuple(get_sku_category, (product_sku,))
        product_sku = set(filter(lambda x: x[2] in strategy_amazonCategoryIdList, get_sku_category_res))
        product_sku = set(map(lambda x: x[0], product_sku))
        return product_sku



    def get_sku_category(self,product_sku):
        source_category_set = self.get_source_category()
        get_sku_category = """
                SELECT sku,other_category_id,amazon_category_id  FROM `test_zhcxkj_center_category`.`t_sku_category` WHERE `platform_type` = '45' AND `sku` IN %s
                        """
        get_sku_category_res = self.db.fetch_all_tuple(get_sku_category, (product_sku,))
        product_sku = set(filter(lambda x: x[1] in source_category_set, get_sku_category_res))
        product_sku = set(map(lambda x: x[0], product_sku))
        return product_sku


    def get_publish_count(self, product_sku):
        if self.source_type == 0:
            publish_count = """
                SELECT source_id,sku,count(*) FROM `test_zhcxkj_center_listing_comprehensive`.`t_walmart_listing` 
                WHERE `source_id` IN %s AND `sku` IN %s GROUP BY source_id,sku
                        """
        else:
            publish_count = """
                    SELECT source_id,sku,count(*) FROM `test_zhcxkj_center_listing_comprehensive`.`t_walmart_wfs_listing` 
                    WHERE `source_id` IN %s AND `sku` IN %s GROUP BY source_id,sku
                            """
        publish_count_res = self.db.fetch_all_tuple(publish_count, (self.source_ids, product_sku,))
        product_sku = set(filter(lambda x: x[2] <= self.skuPublishCountLimit, publish_count_res))
        product_sku = set(map(lambda x: x[0], product_sku))
        return product_sku



    def get_listing_publish_count(self, product_sku):
        if self.source_type == 0:
            publish_count = """
                SELECT sku,count(*) FROM `test_zhcxkj_center_listing_comprehensive`.`t_walmart_listing` 
                WHERE `source_id` IN %s AND `status`=1 AND publish_status=1 AND `sku` IN %s GROUP BY sku
                        """
        else:
            publish_count = """
                    SELECT sku,count(*) FROM `test_zhcxkj_center_listing_comprehensive`.`t_walmart_wfs_listing` 
                    WHERE `source_id` IN %s AND `status`=1 AND publish_status=1 AND `sku` IN %s GROUP BY sku
                            """
        publish_count_res = self.db.fetch_all_tuple(publish_count, (self.source_ids, product_sku,))
        product_sku = set(filter(lambda x: x[1] <= self.listingPublishCountLimit, publish_count_res))
        product_sku = set(map(lambda x: x[0], product_sku))
        return product_sku


    def get_listing(self, product_sku):
        if self.source_type == 0:
            listing = """
                SELECT sku FROM `test_zhcxkj_center_listing_comprehensive`.`t_walmart_listing` 
                WHERE `source_id` IN %s AND `sku` IN %s AND `status`=1 AND publish_status=1
                        """
        else:
            listing = """
                    SELECT sku FROM `test_zhcxkj_center_listing_comprehensive`.`t_walmart_wfs_listing` 
                    WHERE `source_id` IN %s AND `sku` IN %s AND `status`=1
                        """
        listing_res = self.db.fetch_all_tuple(listing, (self.source_ids, product_sku,))
        listing_set = set(map(lambda x: x[0], listing_res))
        product_sku = set(filter(lambda x: x not in listing_set, product_sku))
        return product_sku




    def get_queue(self, product_sku):
        queue = """
                SELECT sku FROM `test_zhcxkj_center_strategy_comprehensive`.`t_walmart_publish_strategy_execute_queue_detail` 
                WHERE source_id IN %s AND sku IN %s AND  business_status IN (0,1,5)
                    """
        queue_res = self.db.fetch_all_tuple(queue, (self.source_ids, product_sku,))
        queue_set = set(map(lambda x: x[0], queue_res))
        product_sku = set(filter(lambda x: x not in queue_set, product_sku))
        return product_sku


    def get_good_num(self, product_sku):
        # 获取店铺渠道配置的发货模板仓库
        ship_template_warehouse_id = """
                SELECT  tl.warehouse_id FROM `test_zhcxkj_center_listing_comprehensive`.`t_walmart_ship_template` t 
                LEFT JOIN `test_zhcxkj_center_listing_comprehensive`.`t_walmart_ship_template_detail` tl 
                ON t.id=tl.ship_template_id WHERE t.`source_id` = %s 
                        """
        ship_template_warehouse_id_res = self.db.fetch_all_tuple(ship_template_warehouse_id, (self.source_ids,))
        ship_template_warehouse_id_set = set(map(lambda x: x[0], ship_template_warehouse_id_res))

        result = {}

        product_sku_list = list(product_sku)
        for sku in product_sku_list:
            is_group_sql = """SELECT is_group FROM test_zhcxkj_center_product.rb_product WHERE sku=%s;"""
            is_group_sql_res = self.db.fetch_all(is_group_sql, (sku,))
            if is_group_sql_res:
                # 非组合sku仓库库存
                if is_group_sql_res[0]['is_group'] == 0:
                    get_good_num = self.get_sku_good_num(sku, ship_template_warehouse_id_set)
                # 组合sku仓库库存
                else:
                    get_good_num = self.get_group_sku_good_num(sku, ship_template_warehouse_id_set)

                # print(f"sku={sku}库存={get_good_num}")

                result[sku] = get_good_num
        result = set(zip(result.keys(), result.values()))
        return result


        #         if get_good_num < self.minQuality:
        #             product_sku.remove(sku)
        # return product_sku


    def get_sku_good_num(self, sku, ship_template_warehouse_id_set):
        filter_local_good_num = """
                SELECT sku,SUM(good_num) good_num FROM test_zhcxkj_center_warehouse.dynamic_inventory WHERE sku=%s AND warehouse_id IN %s;
                        """
        res1 = self.db.fetch_all(filter_local_good_num, (sku, ship_template_warehouse_id_set,))
        filter_overseas_good_num = """
                SELECT hp.sku,SUM(hp.good_num) good_num FROM `test_zhcxkj_center_warehouse`.`rb_ware_house` h 
                LEFT JOIN test_zhcxkj_center_warehouse.rb_ware_house_product hp ON h.id=hp.wareHouse_id 
                WHERE h.ware_house_type>1 AND hp.sku=%s and hp.wareHouse_id IN %s;
                        """
        res2 = self.db.fetch_all(filter_overseas_good_num, (sku, ship_template_warehouse_id_set,))
        res = res1 + res2
        res = list(filter(lambda x: x['good_num'] != None, res))
        if res:
            sku_good_num = sum(map(lambda x: x['good_num'], res))
        else:
            sku_good_num = 0
        return sku_good_num
        # print(f"非组合{sku}的库存为{sku_good_num}")



    def get_group_sku_good_num(self, sku,ship_template_warehouse_id_set):

        get_child_sku = "SELECT sku,sku_num FROM test_zhcxkj_center_product.rb_product_group_sku " \
                        "WHERE group_sku=%s;"
        get_child_sku_res = self.db.fetch_all(get_child_sku, (sku,))
        group_sku_local_good_num = []
        if get_child_sku_res:
            child_set = set(map(lambda x: x['sku'], get_child_sku_res))
            filter_local_good_num = """
                        SELECT sku,warehouse_id,good_num FROM test_zhcxkj_center_warehouse.dynamic_inventory 
                        WHERE sku IN %s AND warehouse_id IN %s group by sku,warehouse_id;
                                """
            filter_local_good_num_res = self.db.fetch_all(filter_local_good_num,
                                                          (child_set, ship_template_warehouse_id_set,))
            filter_overseas_good_num = """
                        SELECT hp.sku,hp.wareHouse_id,good_num FROM `test_zhcxkj_center_warehouse`.`rb_ware_house` h 
                        LEFT JOIN test_zhcxkj_center_warehouse.rb_ware_house_product hp ON h.id=hp.wareHouse_id 
                        WHERE h.ware_house_type>1 AND hp.sku IN %s and hp.wareHouse_id IN %s group by sku,warehouse_id;
                            """
            filter_overseas_good_num_res = self.db.fetch_all(filter_overseas_good_num,
                                                             (child_set, ship_template_warehouse_id_set,))
            filter_good_num_res = list(filter_local_good_num_res) + list(filter_overseas_good_num_res)

            for warehouse_id in ship_template_warehouse_id_set:
                child_sku_good_num = []
                for child_sku in child_set:
                    if any(child_sku in item['sku'] for item in filter_good_num_res):
                        for item in filter_good_num_res:
                            if child_sku in item['sku'] and item.get('warehouse_id') == warehouse_id:
                                sku_num = list(filter(lambda x: x['sku'] == child_sku, get_child_sku_res))
                                child_sku_good_num.append(item.get('good_num')//sku_num[0].get('sku_num'))
                                break
                    else:
                        child_sku_good_num.append(0)
                if child_sku_good_num:
                    group_sku_local_good_num.append(min(child_sku_good_num))
        else:
            group_sku_local_good_num.append(0)
        return sum(group_sku_local_good_num)
        # print(f"组合{sku}的库存为{group_sku_local_good_num}")




if __name__ == '__main__':
    publish_strategy = PublishStrategy(3)
    publish_strategy.get_basices_sku_set()
    # product_sku = {'14975566','16081689','17224433'}
    # ship_template_warehouse_id_set={5312,333}
    # publish_strategy.get_good_num(product_sku)